﻿/*
 * Programer: Jeffrey Padfield
 * Description: SQLite Database
 * Version: 3
 * Date: 01/27/2014
 * Based off the work of: Tigran.
 * Work found at: http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/
 */

using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;

namespace CWSaver.SQLITE
{
    public class SqLiteDatabase
    {
        private SQLiteConnection mDbConnection;

        /// <summary>
        /// Default Constructor.
        /// </summary>
        public SqLiteDatabase()
        {
            
        }

        /// <summary>
        /// Create SQLiteConnection
        /// </summary>
        /// <param name="database">Database to look for.</param>
        public SqLiteDatabase(string database)
        {
            mDbConnection = new SQLiteConnection("Data Source=" + database + ".sqlite; Version=3");
        }

        /// <summary>
        /// Opens the connection to sqLite.
        /// </summary>
        /// <returns>True if connection opens. False if connection failed to open.</returns>
        private bool OpenConnection()
        {
            try
            {
                mDbConnection.Open();
                return true;
            }
            catch (SQLiteException ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Closes the connection to sqLite
        /// </summary>
        /// <returns>True if the connection closes. False if connection failed to close.</returns>
        private bool CloseConnection()
        {
            try
            {
                mDbConnection.Close();
                return true;
            }
            catch (SQLiteException ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Creates all the databases that are used by your program.
        /// </summary>
        /// <param name="dbName">Name of the datbase to create.</param>
        public void CreateDatabase(string dbName)
        {
            if (!File.Exists(dbName + ".sqlite") )
            {
                SQLiteConnection.CreateFile(dbName + ".sqlite");
            }
            else 
            {
                // TODO: Display file already exsists.
            }
        }

        /// <summary>
        /// Creates the tables within the database files.
        /// </summary>
        /// <param name="sqlTable">Create Table string.</param>
        public void CreateTable(string sqlTable)
        {

            if (OpenConnection())
            {
                SQLiteCommand command = new SQLiteCommand(sqlTable, mDbConnection);

                command.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Inserts data into the database
        /// </summary>
        /// <param name="sqlData">Sql code to execute.</param>
        /// <param name="database">Database to use to execute sqlData against.</param>
        public void InsertData(string sqlData)
        {
            if (OpenConnection())
            {
                SQLiteCommand command = new SQLiteCommand(sqlData, mDbConnection);

                command.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Updates a row in the database.
        /// </summary>
        /// <param name="sqlData">Sql code to execute.</param>
        /// <param name="database">Database to use to execute sqlData against.</param>
        public void UpdateData(string sqlData)
        {
            if (OpenConnection())
            {
                SQLiteCommand command = new SQLiteCommand(sqlData, mDbConnection);

                command.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Deletes data from the database.
        /// </summary>
        /// <param name="sqlData">Sql code to execute.</param>
        /// <param name="database">Database to use to execute sqlData against.</param>
        public void DeleteData(string sqlData)
        {
            if (OpenConnection())
            {
                SQLiteCommand command = new SQLiteCommand(sqlData, mDbConnection);

                command.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Returns data from database.
        /// </summary>
        /// <param name="sqlData">Sql code to execute.</param>
        /// <param name="columns">Names of columns to pull data out of.</param>
        /// <returns>Column information.</returns>
        public List<string>[] GetData(string sqlData, string[] columns)
        {
            List<string>[] dataList = new List<string>[columns.Count()];

            for (int i = 0; i < columns.Count(); i ++)
            {
                dataList[i] = new List<string>();
            }

            if (OpenConnection())
            {
                SQLiteCommand command = new SQLiteCommand(sqlData, mDbConnection);
                
                SQLiteDataReader reader = command.ExecuteReader();

                // Make sure reader is not null yet.
                while (reader.Read())
                {
                    for (int i = 0; i < columns.Count(); i++)
                    {
                        dataList[i].Add(reader[columns[i]] + "");
                    }
                }

                CloseConnection();

                return dataList;
            }
            else
            {
                return dataList;
            }
        }

        /// <summary>
        /// Counts how many items are in a database
        /// </summary>
        /// <param name="sqlData">Sql code to execute.</param>
        /// <param name="databae">Database to use to execute sqlData against.</param>
        /// <returns></returns>
        public int CountData(string sqlData)
        {
            int count = -1;
            if (OpenConnection())
            {
                SQLiteCommand cmd = new SQLiteCommand(sqlData, mDbConnection);

                count = int.Parse(cmd.ExecuteScalar() + "");

                CloseConnection();

                return count;
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// Backups the entire database
        /// </summary>
        /// <param name="database">Database to backup.</param>
        public void Backup(string database)
        {
            // TODO: Figure out how to back up SqLite database.
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="database"></param>
        public void Restore(string database)
        {
            // TODO: Figure out how to restore SqLite database
        }
    }
}
